package LawsonTransformJava.pgm;
//OracleEmployeeDAO implementation of the 
//EmployeeDAO interface. This class can contain all
//Oracle specific code and SQL statements. 
//The client is thus shielded from knowing 
//these implementation details.

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.io.*;
import java.lang.reflect.InvocationTargetException;

import LawsonTransformJava.xref.CrossRef;
import LawsonTransformJava.xref.XrefJobCode;
import LawsonTransformJava.xref.XrefPosition;
import LawsonTransformJava.xref.XrefDepartment;
import LawsonTransformJava.xref.XrefProcessLevel; 
import LawsonTransformJava.xref.XrefLocatCode;
import LawsonTransformJava.xref.XrefUnionCode;

import LawsonTransformJava.dao.DAOFactoryAccessDB;
import LawsonTransformJava.dao.DAOFactoryOracleDB;
import LawsonTransformJava.dao.DAOPAEmpPos;
import LawsonTransformJava.dao.DAObject;
import LawsonTransformJava.data.TblPAEmpPos;

public class PA513 extends DAObject implements 
DAOPAEmpPos, Runnable {

	private XrefJobCode xJobCodes = null;
	private XrefPosition xPositions = null;
	private XrefDepartment xDepartments = null;
	private XrefProcessLevel xProcessLevels = null;
	//	private XrefEmpStatus xEmpStatuses = null;
	private XrefLocatCode xLocatCodes = null;
	private XrefUnionCode xUnionCodes = null;
	
	TblPAEmpPos pep = null;

	public PA513(File output, File errOutput, File errReport, boolean allowBlanks) throws SQLException {
		// initialization 
		try {
			this.output = new FileWriter(this.outputFile = output);
			this.errOutput = new FileWriter(this.errOutputFile = errOutput);
			this.errReport = new Report(errReport, new Date());

			//initialize
			init(allowBlanks);
		} catch (IOException e) {
			// Could no write properties file
			System.out.println("IOException: " + e);
		}
	}

	private void init(boolean allowBlanks) throws IOException, SQLException {
		// Set the data and xref variables
//		data = new DAOFactoryOracleDB.getDAOOracleDB(true);
		data = DAOFactoryOracleDB.getDAOOracleDB(true);
		xJobCodes = new XrefJobCode(allowBlanks);
		xPositions = new XrefPosition();
		xDepartments = new XrefDepartment(allowBlanks);
		xProcessLevels = new XrefProcessLevel();
		xLocatCodes = new XrefLocatCode(allowBlanks);
		xUnionCodes = new XrefUnionCode(allowBlanks);

		errReport.setPgmName(this.getClass().getSimpleName());

		sqlData = "SELECT * FROM " + data.getSchema() + "." + TABLE_NAME;
		stmtData = data.getDataStatement();	    
		countStmtData = data.getCountStatement();
		//Set the Date Format for output
		df = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
	}

	public TblPAEmpPos findPAEmpPos() {
		try {
			String schemax=data.getSchema();			
//			TblPAEmpPos pep = new TblPAEmpPos();
			TblPAEmpPos pep = null;
			sqlData = "SELECT " +
					schemax + "." + TABLE_NAME  + ".EMPLOYEE, " +
					schemax + "." + TABLE_NAME  + ".POS_LEVEL, " + 
					schemax + "." + TABLE_NAME  + ".EFFECT_DATE, " +
					schemax + "." + TABLE_NAME  + ".END_DATE, " + 
					schemax + "." + TABLE_NAME  + ".POSITION, " +  
					schemax + "." + TABLE_NAME  + ".JOB_CODE, " +  
					schemax + "." + TABLE_NAME  + ".PROCESS_LEVEL, " +
					schemax + "." + TABLE_NAME  + ".DEPARTMENT, " +  
					schemax + "." + TABLE_NAME  + ".USER_LEVEL, " +  
					schemax + "." + TABLE_NAME  + ".SUPERVISOR, " +  
					schemax + "." + TABLE_NAME  + ".SUPERVISOR_IND, " +
					schemax + "." + TABLE_NAME  + ".LOCAT_CODE, " +  
					schemax + "." + TABLE_NAME  + ".UNION_CODE, " +  
					schemax + "." + TABLE_NAME  + ".FTE, " + 
					schemax + "." + TABLE_NAME  + ".PAY_RATE, " + 
					schemax + "." + TABLE_NAME  + ".SCHEDULE, " +
					schemax + "." + TABLE_NAME  + ".PAY_GRADE, " + 
					schemax + "." + TABLE_NAME  + ".PAY_STEP, " + 
					schemax + "." + TABLE_NAME  + ".WORK_SCHED, " + 
					schemax + "." + TABLE_NAME  + ".R_SHIFT, " + 
					schemax + "." + TABLE_NAME  + ".PRO_RATE_A_SAL, " +
					schemax + "." + TABLE_NAME  + ".ANNUAL_HOURS, " + 
					schemax + "." + TABLE_NAME  + ".EXP_COMPANY, " + 
					schemax + "." + TABLE_NAME  + ".EXP_ACCT_UNIT, " + 
					schemax + "." + TABLE_NAME  + ".EXP_ACCOUNT, " + 
					schemax + "." + TABLE_NAME  + ".EXP_SUB_ACCT, " + 
					schemax + "." + TABLE_NAME  + ".ACTIVITY, " + 
					schemax + "." + TABLE_NAME  + ".ACCT_CATEGORY, " + 
					schemax + "." + TABLE_NAME  + ".DATE_STAMP, " + 
					schemax + "." + TABLE_NAME  + ".TIME_STAMP, " +
					schemax + "." + TABLE_NAME  + ".USER_ID, " + 
					schemax + "." + TABLE_NAME  + ".CURRENCY_CODE, " + 
					schemax + "." + TABLE_NAME  + ".BASE_CURRENCY, " + 
					schemax + "." + TABLE_NAME  + ".BASE_PAY_RATE, " +
					schemax + "." + TABLE_NAME  + ".BARGAIN_UNIT, " + 
					schemax + "." + TABLE_NAME  + ".DATE_ASSIGN, " + 
					schemax + "." + TABLE_NAME  + ".USER_AMOUNT, " + 
					schemax + "." + TABLE_NAME2 + ".SALARY_CLASS " +
					"FROM "        + schemax + "." + TABLE_NAME  +
					" INNER JOIN " + schemax + "." + TABLE_NAME2 +
					" ON "         + schemax + "." + TABLE_NAME  + ".POSITION" +
					"="	       + schemax + "." + TABLE_NAME2 + ".POSITION" +
					" " + (whereClause==null?"":whereClause);

			rslt = data.getDataStatement().executeQuery(sqlData);
			rsmd = rslt.getMetaData();
			if (rslt.next())
				hasMoreRecs = true;
			else
				hasMoreRecs = false;
			pep = (TblPAEmpPos) createObject(rslt, new TblPAEmpPos());
			//			heu.setHRUserFlds((new HR515HRUserFlds()).findHRUserFlds(heu.getField_key()));	  
			return pep;
		} catch (SQLException e) {
			// Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (InvocationTargetException ite) {
			System.out.println("InvocationException: " + ite);
		} catch (IllegalAccessException iae) {
			System.out.println("IllegalAccessException: " + iae);
		}
		return null;
	}

	public TblPAEmpPos findPAEmpPos(String employeeNumber) {
		try {	

			String schemax=data.getSchema();		
//			TblPAEmpPos pep = new TblPAEmpPos();
//			TblPAEmpPos pep = null;
			//		sqlData = "SELECT * FROM " + data.getSchema() + "." + TABLE_NAME + " WHERE " + TABLE_NAME + "='" + employeeNumber + "'";

			sqlData = "SELECT " +
					schemax + "." + TABLE_NAME  + ".EMPLOYEE, " +
					schemax + "." + TABLE_NAME  + ".POS_LEVEL, " + 
					schemax + "." + TABLE_NAME  + ".EFFECT_DATE, " +
					schemax + "." + TABLE_NAME  + ".END_DATE, " + 
					schemax + "." + TABLE_NAME  + ".POSITION, " +  
					schemax + "." + TABLE_NAME  + ".JOB_CODE, " +  
					schemax + "." + TABLE_NAME  + ".PROCESS_LEVEL, " +
					schemax + "." + TABLE_NAME  + ".DEPARTMENT, " +  
					schemax + "." + TABLE_NAME  + ".USER_LEVEL, " +  
					schemax + "." + TABLE_NAME  + ".SUPERVISOR, " +  
					schemax + "." + TABLE_NAME  + ".SUPERVISOR_IND, " +
					schemax + "." + TABLE_NAME  + ".LOCAT_CODE, " +  
					schemax + "." + TABLE_NAME  + ".UNION_CODE, " +  
					schemax + "." + TABLE_NAME  + ".FTE, " + 
					schemax + "." + TABLE_NAME  + ".PAY_RATE, " + 
					schemax + "." + TABLE_NAME  + ".SCHEDULE, " +
					schemax + "." + TABLE_NAME  + ".PAY_GRADE, " + 
					schemax + "." + TABLE_NAME  + ".PAY_STEP, " + 
					schemax + "." + TABLE_NAME  + ".WORK_SCHED, " + 
					schemax + "." + TABLE_NAME  + ".R_SHIFT, " + 
					schemax + "." + TABLE_NAME  + ".PRO_RATE_A_SAL, " +
					schemax + "." + TABLE_NAME  + ".ANNUAL_HOURS, " + 
					schemax + "." + TABLE_NAME  + ".EXP_COMPANY, " + 
					schemax + "." + TABLE_NAME  + ".EXP_ACCT_UNIT, " + 
					schemax + "." + TABLE_NAME  + ".EXP_ACCOUNT, " + 
					schemax + "." + TABLE_NAME  + ".EXP_SUB_ACCT, " + 
					schemax + "." + TABLE_NAME  + ".ACTIVITY, " + 
					schemax + "." + TABLE_NAME  + ".ACCT_CATEGORY, " + 
					schemax + "." + TABLE_NAME  + ".DATE_STAMP, " + 
					schemax + "." + TABLE_NAME  + ".TIME_STAMP, " +
					schemax + "." + TABLE_NAME  + ".USER_ID, " + 
					schemax + "." + TABLE_NAME  + ".CURRENCY_CODE, " + 
					schemax + "." + TABLE_NAME  + ".BASE_CURRENCY, " + 
					schemax + "." + TABLE_NAME  + ".BASE_PAY_RATE, " +
					schemax + "." + TABLE_NAME  + ".BARGAIN_UNIT, " + 
					schemax + "." + TABLE_NAME  + ".DATE_ASSIGN, " + 
					schemax + "." + TABLE_NAME  + ".USER_AMOUNT, " + 
					schemax + "." + TABLE_NAME2 + ".SALARY_CLASS " +
					"FROM "        + schemax + "." + TABLE_NAME  +
					" LEFT JOIN " + schemax + "." + TABLE_NAME2 +
					" ON "         + schemax + "." + TABLE_NAME  + ".POSITION" +
					"="	       + schemax + "." + TABLE_NAME2 + ".POSITION" +
			        " " + (whereClause==null?"":whereClause);
			rslt = data.getDataStatement().executeQuery(sqlData);
			rsmd = rslt.getMetaData();
			if (rslt.next())
				hasMoreRecs = true;
			else
				hasMoreRecs = false;
			//@ TODO Add the creation of an Employee object here
			pep = (TblPAEmpPos) createObject(rslt, new TblPAEmpPos());
			//		String temp_tc = pep.getPosition();
			//		pep.setPAPosition((new PA513PAPosition()).findPosition(pep.getPosition()));
			return pep;
			//createFields(pep);
		} catch (SQLException e) {
			// Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (InvocationTargetException ite) {
			System.out.println("InvocationException: " + ite);
		} catch (IllegalAccessException iae) {
			System.out.println("IllegalAccessException: " + iae);
		}
		return new TblPAEmpPos();
	}

	public TblPAEmpPos nextPAEmpPos() {

//		TblPAEmpPos pep = new TblPAEmpPos();
		try {
			if (rslt.next()) {
				hasMoreRecs = true;
				pep = (TblPAEmpPos) createObject(rslt, new TblPAEmpPos());
			} else
				hasMoreRecs = false;
			return pep;
		} catch (SQLException e) {
			// Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (InvocationTargetException ite) {
			System.out.println("InvocationException: " + ite);
		} catch (IllegalAccessException iae) {
			System.out.println("IllegalAccessException: " + iae);
		}
		return null;
	}

	public boolean hasMore () {
		return hasMoreRecs;
	}

	public int countPAEmpPos() {
		try {
			countRecs = data.getCountStatement().executeQuery("SELECT COUNT(*) " +
					"FROM " + data.getSchema() + "." + TABLE_NAME + " " + 
					"INNER JOIN " + data.getSchema() + "." + TABLE_NAME2 + " " +
						"ON " + TABLE_NAME + ".POSITION=" + TABLE_NAME2 + ".POSITION " +
//					FROM PRODLAW.PAEMPPOS INNER JOIN PRODLAW.PAPOSITION ON PRODLAW.PAEMPPOS.POSITION=PRODLAW.PAPOSITION.POSITION
					(whereClause==null?"":whereClause));
			countRecs.next();
			return recCount=countRecs.getInt(1);
		} catch (SQLException e) {
			// Could not connect to the database
			System.out.println("SQLException: " + e);
		}
		return 0;
	}

	public void setWhereClause(String where) {
		this.whereClause = where;
	}

	public void run() {

		try {
			//Update current date/time
			System.out.println(errReport.getName() + " Start " + Report.getDateFormat().format(new Date()));

			pep = findPAEmpPos();
			while (hasMore()) {
				writePA513Layout(pep);
				pep = nextPAEmpPos();
			}
			//Update current date/time
			System.out.println(errReport.getName() + " Finish " + Report.getDateFormat().format(new Date()));

			//Write Record Counts
			errReport.writeRecordCounts(this.countPAEmpPos());
			
			//Reclaim Resources
			fileCleanup();

		} catch (IOException e) {
			// Could not write properties file
			// Could not create output file
			System.out.println("IOException: " + e);
		}

	}

	//Output the PA513 format
	public void writePA513Layout(TblPAEmpPos pep) throws IOException {

		String writeValue = null;

		writeValue =
				"\""+"A"+"\"" + "," +	
				"1" + "," +
				pep.getEmployee() + "," +
				pep.getPos_level() + "," +
				pep.getEffect_date() + "," +
				pep.getEnd_date() + "," +
				xPositions.xlate(pep.getPosition()) + "," +
				xJobCodes.xlate(pep.getJob_code()) + "," +
				xProcessLevels.xlate(pep.getProcess_level()) + "," +
				pep.getDepartment() + "," +								//Department
//				xDepartments.xlate(pep.getDepartment()) + "," +			//Department
				pep.getUser_level() + "," +
				pep.getSupervisor() + "," +
				pep.getSupervisor_ind() + "," +
				xLocatCodes.xlate(pep.getLocat_code()) + "," +
				pep.getUnion_code() + "," +								//Union Code
//				xUnionCodes.xlate(pep.getUnion_code()) + "," +			//Union Code
				pep.getFte() + "," +
				pep.getPay_rate() + "," +
				pep.getSchedule() + "," +
				pep.getPay_grade() + "," +
				pep.getPay_step() + "," +
				pep.getWork_sched() + "," +
				pep.getR_shift() + "," +
				pep.getPro_rate_a_sal() + "," +
				pep.getAnnual_hours() + "," +
//				pep.getExp_company() + "," +	//Exp Company
				"1," +							//Exp Company
				//@ TODO remove hard coded acct unit & account
//				pep.getExp_acct_unit() + "," +
//				pep.getExp_account() + "," +
//				pep.getExp_sub_acct() + "," +
				"99000," +
				"39999," +
				"0,"      +
				pep.getActivity() + "," +
				pep.getAcct_category() + "," +
				pep.getDate_stamp() + "," +
				pep.getTime_stamp() + "," +
				pep.getUser_id() + "," +
				pep.getCurrency_code() + "," +
				pep.getBase_currency() + "," +
				pep.getBase_pay_rate() + "," +
				pep.getSalary_class()  + "," +
				pep.getBargain_unit() + "," +
				pep.getDate_assign() + "," +
				pep.getUser_amount()
			;
		
		if (CrossRef.isError()) {
			//Increment Record in Error
			errReport.addErrorCnt();
			//Write to Error File
			this.errOutput.write(writeValue + "\n");
			this.errOutput.flush();
			CrossRef.resetError();
		}
		else {
			//Increment Record Written
			errReport.addWrittenCnt();
			//Write to Load File
			this.output.write(writeValue + "\n");
			this.output.flush();
		}
	}
	
	public void fileCleanup() throws IOException {
		errReport.flush();
		errReport.close();
		output.flush();
		output.close();
		errOutput.flush();
		errOutput.close();
	}
}